{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SELECT from WORLD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      " ·········\n"
     ]
    }
   ],
   "source": [
    "# Prerequesites\n",
    "import getpass\n",
    "%load_ext sql\n",
    "pwd = getpass.getpass()\n",
    "# %sql mysql+pymysql://root:$pwd@localhost:3306/sqlzoo\n",
    "%sql postgresql://postgres:$pwd@localhost/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Introduction\n",
    "\n",
    "[Read the notes about this table](https://sqlzoo.net/wiki/Read_the_notes_about_this_table.). Observe the result of running this SQL command to show the name, continent and population of all countries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "195 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>continent</th>\n",
       "        <th>population</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Afghanistan</td>\n",
       "        <td>Asia</td>\n",
       "        <td>25500100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Albania</td>\n",
       "        <td>Europe</td>\n",
       "        <td>2821977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Algeria</td>\n",
       "        <td>Africa</td>\n",
       "        <td>38700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Andorra</td>\n",
       "        <td>Europe</td>\n",
       "        <td>76098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Angola</td>\n",
       "        <td>Africa</td>\n",
       "        <td>19183590</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Antigua and Barbuda</td>\n",
       "        <td>Caribbean</td>\n",
       "        <td>86295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Argentina</td>\n",
       "        <td>South America</td>\n",
       "        <td>42669500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Armenia</td>\n",
       "        <td>Eurasia</td>\n",
       "        <td>3017400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Australia</td>\n",
       "        <td>Oceania</td>\n",
       "        <td>23545500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Austria</td>\n",
       "        <td>Europe</td>\n",
       "        <td>8504850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Azerbaijan</td>\n",
       "        <td>Asia</td>\n",
       "        <td>9477100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bahamas</td>\n",
       "        <td>Caribbean</td>\n",
       "        <td>351461</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bahrain</td>\n",
       "        <td>Asia</td>\n",
       "        <td>1234571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bangladesh</td>\n",
       "        <td>Asia</td>\n",
       "        <td>156557000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Barbados</td>\n",
       "        <td>Caribbean</td>\n",
       "        <td>285000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Belarus</td>\n",
       "        <td>Europe</td>\n",
       "        <td>9467000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Belgium</td>\n",
       "        <td>Europe</td>\n",
       "        <td>11198638</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Belize</td>\n",
       "        <td>North America</td>\n",
       "        <td>349728</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Benin</td>\n",
       "        <td>Africa</td>\n",
       "        <td>9988068</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bhutan</td>\n",
       "        <td>Asia</td>\n",
       "        <td>749090</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">195 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('Afghanistan', 'Asia', Decimal('25500100')),\n",
       " ('Albania', 'Europe', Decimal('2821977')),\n",
       " ('Algeria', 'Africa', Decimal('38700000')),\n",
       " ('Andorra', 'Europe', Decimal('76098')),\n",
       " ('Angola', 'Africa', Decimal('19183590')),\n",
       " ('Antigua and Barbuda', 'Caribbean', Decimal('86295')),\n",
       " ('Argentina', 'South America', Decimal('42669500')),\n",
       " ('Armenia', 'Eurasia', Decimal('3017400')),\n",
       " ('Australia', 'Oceania', Decimal('23545500')),\n",
       " ('Austria', 'Europe', Decimal('8504850')),\n",
       " ('Azerbaijan', 'Asia', Decimal('9477100')),\n",
       " ('Bahamas', 'Caribbean', Decimal('351461')),\n",
       " ('Bahrain', 'Asia', Decimal('1234571')),\n",
       " ('Bangladesh', 'Asia', Decimal('156557000')),\n",
       " ('Barbados', 'Caribbean', Decimal('285000')),\n",
       " ('Belarus', 'Europe', Decimal('9467000')),\n",
       " ('Belgium', 'Europe', Decimal('11198638')),\n",
       " ('Belize', 'North America', Decimal('349728')),\n",
       " ('Benin', 'Africa', Decimal('9988068')),\n",
       " ('Bhutan', 'Asia', Decimal('749090')),\n",
       " ('Bolivia', 'South America', Decimal('10027254')),\n",
       " ('Bosnia and Herzegovina', 'Europe', Decimal('3791622')),\n",
       " ('Botswana', 'Africa', Decimal('2024904')),\n",
       " ('Brazil', 'South America', Decimal('202794000')),\n",
       " ('Brunei', 'Asia', Decimal('393162')),\n",
       " ('Bulgaria', 'Europe', Decimal('7245677')),\n",
       " ('Burkina Faso', 'Africa', Decimal('17322796')),\n",
       " ('Burundi', 'Africa', Decimal('9420248')),\n",
       " ('Cambodia', 'Asia', Decimal('15184116')),\n",
       " ('Cameroon', 'Africa', Decimal('20386799')),\n",
       " ('Canada', 'North America', Decimal('35427524')),\n",
       " ('Cape Verde', 'Africa', Decimal('491875')),\n",
       " ('Central African Republic', 'Africa', Decimal('4709000')),\n",
       " ('Chad', 'Africa', Decimal('13211000')),\n",
       " ('Chile', 'South America', Decimal('17773000')),\n",
       " ('China', 'Asia', Decimal('1365370000')),\n",
       " ('Colombia', 'South America', Decimal('47662000')),\n",
       " ('Comoros', 'Africa', Decimal('743798')),\n",
       " ('Congo, Democratic Republic of', 'Africa', Decimal('69360000')),\n",
       " ('Congo, Republic of', 'Africa', Decimal('4559000')),\n",
       " ('Costa Rica', 'North America', Decimal('4667096')),\n",
       " (\"Côte d'Ivoire\", 'Africa', Decimal('23919000')),\n",
       " ('Croatia', 'Europe', Decimal('4290612')),\n",
       " ('Cuba', 'Caribbean', Decimal('11167325')),\n",
       " ('Cyprus', 'Asia', Decimal('865878')),\n",
       " ('Czech Republic', 'Europe', Decimal('10517400')),\n",
       " ('Denmark', 'Europe', Decimal('5634437')),\n",
       " ('Djibouti', 'Africa', Decimal('886000')),\n",
       " ('Dominica', 'Caribbean', Decimal('71293')),\n",
       " ('Dominican Republic', 'Caribbean', Decimal('9445281')),\n",
       " ('Ecuador', 'South America', Decimal('15774200')),\n",
       " ('Egypt', 'Asia', Decimal('86736900')),\n",
       " ('El Salvador', 'North America', Decimal('6384000')),\n",
       " ('Equatorial Guinea', 'Africa', Decimal('1622000')),\n",
       " ('Eritrea', 'Africa', Decimal('6536000')),\n",
       " ('Estonia', 'Europe', Decimal('1315819')),\n",
       " ('Ethiopia', 'Africa', Decimal('87952991')),\n",
       " ('Fiji', 'Oceania', Decimal('858038')),\n",
       " ('Finland', 'Europe', Decimal('5458038')),\n",
       " ('France', 'Europe', Decimal('65906000')),\n",
       " ('Gabon', 'Africa', Decimal('1711000')),\n",
       " ('Gambia', 'Africa', Decimal('1882450')),\n",
       " ('Georgia', 'Asia', Decimal('4490500')),\n",
       " ('Germany', 'Europe', Decimal('80716000')),\n",
       " ('Ghana', 'Africa', Decimal('27043093')),\n",
       " ('Greece', 'Europe', Decimal('11123034')),\n",
       " ('Grenada', 'Caribbean', Decimal('103328')),\n",
       " ('Guatemala', 'North America', Decimal('15806675')),\n",
       " ('Guinea', 'Africa', Decimal('10824200')),\n",
       " ('Guinea-Bissau', 'Africa', Decimal('1746000')),\n",
       " ('Guyana', 'South America', Decimal('784894')),\n",
       " ('Haiti', 'Caribbean', Decimal('10413211')),\n",
       " ('Honduras', 'North America', Decimal('8555072')),\n",
       " ('Hungary', 'Europe', Decimal('9879000')),\n",
       " ('Iceland', 'Europe', Decimal('326340')),\n",
       " ('India', 'Asia', Decimal('1246160000')),\n",
       " ('Indonesia', 'Asia', Decimal('252164800')),\n",
       " ('Iran', 'Asia', Decimal('77552000')),\n",
       " ('Iraq', 'Asia', Decimal('36004552')),\n",
       " ('Ireland', 'Europe', Decimal('4593100')),\n",
       " ('Israel', 'Asia', Decimal('8193900')),\n",
       " ('Italy', 'Europe', Decimal('60782668')),\n",
       " ('Jamaica', 'Caribbean', Decimal('2717991')),\n",
       " ('Japan', 'Asia', Decimal('127090000')),\n",
       " ('Jordan', 'Asia', Decimal('6602190')),\n",
       " ('Kazakhstan', 'Europe', Decimal('17244000')),\n",
       " ('Kenya', 'Africa', Decimal('45546000')),\n",
       " ('Kiribati', 'Oceania', Decimal('106461')),\n",
       " ('Kuwait', 'Asia', Decimal('3065850')),\n",
       " ('Kyrgyzstan', 'Asia', Decimal('5776570')),\n",
       " ('Laos', 'Asia', Decimal('6580800')),\n",
       " ('Latvia', 'Europe', Decimal('1996500')),\n",
       " ('Lebanon', 'Asia', Decimal('4966000')),\n",
       " ('Lesotho', 'Africa', Decimal('2098000')),\n",
       " ('Liberia', 'Africa', Decimal('4397000')),\n",
       " ('Libya', 'Africa', Decimal('6253000')),\n",
       " ('Liechtenstein', 'Europe', Decimal('37132')),\n",
       " ('Lithuania', 'Europe', Decimal('2939431')),\n",
       " ('Luxembourg', 'Europe', Decimal('549700')),\n",
       " ('Macedonia', 'Europe', Decimal('2062294')),\n",
       " ('Madagascar', 'Africa', Decimal('21263403')),\n",
       " ('Malawi', 'Africa', Decimal('16829000')),\n",
       " ('Malaysia', 'Asia', Decimal('30177000')),\n",
       " ('Maldives', 'Asia', Decimal('317280')),\n",
       " ('Mali', 'Africa', Decimal('15768000')),\n",
       " ('Malta', 'Europe', Decimal('416055')),\n",
       " ('Marshall Islands', 'Oceania', Decimal('56086')),\n",
       " ('Mauritania', 'Africa', Decimal('3461041')),\n",
       " ('Mauritius', 'Africa', Decimal('1257900')),\n",
       " ('Mexico', 'North America', Decimal('119713203')),\n",
       " ('Micronesia, Federated States of', 'Oceania', Decimal('101351')),\n",
       " ('Moldova', 'Europe', Decimal('3557600')),\n",
       " ('Monaco', 'Europe', Decimal('36950')),\n",
       " ('Mongolia', 'Asia', Decimal('2931300')),\n",
       " ('Montenegro', 'Europe', Decimal('620029')),\n",
       " ('Morocco', 'Africa', Decimal('33307500')),\n",
       " ('Mozambique', 'Africa', Decimal('23700715')),\n",
       " ('Myanmar', 'Asia', Decimal('61120000')),\n",
       " ('Namibia', 'Africa', Decimal('2113077')),\n",
       " ('Nauru', 'Oceania', Decimal('9945')),\n",
       " ('Nepal', 'Asia', Decimal('26494504')),\n",
       " ('Netherlands', 'Europe', Decimal('16857500')),\n",
       " ('New Zealand', 'Oceania', Decimal('4538520')),\n",
       " ('Nicaragua', 'North America', Decimal('6071045')),\n",
       " ('Niger', 'Africa', Decimal('17138707')),\n",
       " ('Nigeria', 'Africa', Decimal('178517000')),\n",
       " ('North Korea', 'Asia', Decimal('25027000')),\n",
       " ('Norway', 'Europe', Decimal('5124383')),\n",
       " ('Oman', 'Asia', Decimal('4020000')),\n",
       " ('Pakistan', 'Asia', Decimal('188020000')),\n",
       " ('Palau', 'Oceania', Decimal('20901')),\n",
       " ('Panama', 'North America', Decimal('3405813')),\n",
       " ('Papua New Guinea', 'Oceania', Decimal('7398500')),\n",
       " ('Paraguay', 'South America', Decimal('6783374')),\n",
       " ('Peru', 'South America', Decimal('30475144')),\n",
       " ('Philippines', 'Asia', Decimal('99804200')),\n",
       " ('Poland', 'Europe', Decimal('38496000')),\n",
       " ('Portugal', 'Europe', Decimal('10477800')),\n",
       " ('Qatar', 'Asia', Decimal('2174035')),\n",
       " ('Romania', 'Europe', Decimal('19942642')),\n",
       " ('Russia', 'Eurasia', Decimal('146000000')),\n",
       " ('Rwanda', 'Africa', Decimal('10537222')),\n",
       " ('Saint Kitts and Nevis', 'North America', Decimal('55000')),\n",
       " ('Saint Lucia', 'Caribbean', Decimal('180000')),\n",
       " ('Saint Vincent and the Grenadines', 'South America', Decimal('109000')),\n",
       " ('Samoa', 'Oceania', Decimal('187820')),\n",
       " ('San Marino', 'Europe', Decimal('32637')),\n",
       " ('Sao Tomé and Príncipe', 'Africa', Decimal('190428')),\n",
       " ('Saudi Arabia', 'Asia', Decimal('29994272')),\n",
       " ('Senegal', 'Africa', Decimal('12873601')),\n",
       " ('Serbia', 'Europe', Decimal('7181505')),\n",
       " ('Seychelles', 'Africa', Decimal('90945')),\n",
       " ('Sierra Leone', 'Africa', Decimal('6190280')),\n",
       " ('Singapore', 'Asia', Decimal('5399200')),\n",
       " ('Slovakia', 'Europe', Decimal('5415949')),\n",
       " ('Slovenia', 'Europe', Decimal('2063151')),\n",
       " ('Solomon Islands', 'Oceania', Decimal('581344')),\n",
       " ('Somalia', 'Africa', Decimal('10806000')),\n",
       " ('South Africa', 'Africa', Decimal('52981991')),\n",
       " ('South Korea', 'Asia', Decimal('50423955')),\n",
       " ('South Sudan', 'Africa', Decimal('11739000')),\n",
       " ('Spain', 'Europe', Decimal('46609700')),\n",
       " ('Sri Lanka', 'Asia', Decimal('20277597')),\n",
       " ('Sudan', 'Africa', Decimal('37289406')),\n",
       " ('Suriname', 'South America', Decimal('534189')),\n",
       " ('Swaziland', 'Africa', Decimal('1268000')),\n",
       " ('Sweden', 'Europe', Decimal('9675885')),\n",
       " ('Switzerland', 'Europe', Decimal('8160900')),\n",
       " ('Syria', 'Asia', Decimal('21987000')),\n",
       " ('Taiwan', 'Asia', Decimal('23386883')),\n",
       " ('Tajikistan', 'Asia', Decimal('8160000')),\n",
       " ('Tanzania', 'Africa', Decimal('44928923')),\n",
       " ('Thailand', 'Asia', Decimal('64456700')),\n",
       " ('Timor-Leste', 'Asia', Decimal('1212107')),\n",
       " ('Togo', 'Africa', Decimal('6993000')),\n",
       " ('Tonga', 'Oceania', Decimal('103036')),\n",
       " ('Trinidad and Tobago', 'Caribbean', Decimal('1328019')),\n",
       " ('Tunisia', 'Africa', Decimal('10886500')),\n",
       " ('Turkey', 'Asia', Decimal('76667864')),\n",
       " ('Turkmenistan', 'Asia', Decimal('5307000')),\n",
       " ('Tuvalu', 'Oceania', Decimal('11323')),\n",
       " ('Uganda', 'Africa', Decimal('35357000')),\n",
       " ('Ukraine', 'Europe', Decimal('43009258')),\n",
       " ('United Arab Emirates', 'Asia', Decimal('9446000')),\n",
       " ('United Kingdom', 'Europe', Decimal('64105700')),\n",
       " ('United States', 'North America', Decimal('318320000')),\n",
       " ('Uruguay', 'South America', Decimal('3286314')),\n",
       " ('Uzbekistan', 'Asia', Decimal('30492800')),\n",
       " ('Vanuatu', 'Oceania', Decimal('264652')),\n",
       " ('Vatican City', 'Europe', Decimal('839')),\n",
       " ('Venezuela', 'South America', Decimal('28946101')),\n",
       " ('Vietnam', 'Asia', Decimal('89708900')),\n",
       " ('Yemen', 'Asia', Decimal('25235000')),\n",
       " ('Zambia', 'Africa', Decimal('15023315')),\n",
       " ('Zimbabwe', 'Africa', Decimal('13061239'))]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, continent, population FROM world;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Large Countries\n",
    "\n",
    "[How to use WHERE to filter records](https://sqlzoo.net/wiki/WHERE_filters). Show the name for the countries that have a population of at least 200 million. 200 million is 200000000, there are eight zeros."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>India</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Indonesia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United States</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Brazil',), ('China',), ('India',), ('Indonesia',), ('United States',)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name FROM world WHERE population>=200000000;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Per capita GDP\n",
    "\n",
    "Give the `name` and the **per capita GDP** for those countries with a `population` of at least 200 million.\n",
    "\n",
    "> _HELP:How to calculate per capita GDP_   \n",
    "> per capita GDP is the GDP divided by the population GDP/population"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>pcg</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brazil</td>\n",
       "        <td>11115.2647514226259160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>6121.7105985923229601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>India</td>\n",
       "        <td>1504.7931244783976375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Indonesia</td>\n",
       "        <td>3482.0204881886766115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United States</td>\n",
       "        <td>51032.294546368434</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Brazil', Decimal('11115.2647514226259160')),\n",
       " ('China', Decimal('6121.7105985923229601')),\n",
       " ('India', Decimal('1504.7931244783976375')),\n",
       " ('Indonesia', Decimal('3482.0204881886766115')),\n",
       " ('United States', Decimal('51032.294546368434'))]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, gdp/population as pcg FROM world WHERE population > 200000000;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. South America In millions\n",
    "\n",
    "Show the `name` and `population` in millions for the countries of the `continent` 'South America'. Divide the population by 1000000 to get population in millions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "13 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>population</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Argentina</td>\n",
       "        <td>42.6695000000000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bolivia</td>\n",
       "        <td>10.0272540000000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brazil</td>\n",
       "        <td>202.7940000000000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Chile</td>\n",
       "        <td>17.7730000000000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Colombia</td>\n",
       "        <td>47.6620000000000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ecuador</td>\n",
       "        <td>15.7742000000000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Guyana</td>\n",
       "        <td>0.78489400000000000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Paraguay</td>\n",
       "        <td>6.7833740000000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Peru</td>\n",
       "        <td>30.4751440000000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Saint Vincent and the Grenadines</td>\n",
       "        <td>0.10900000000000000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Suriname</td>\n",
       "        <td>0.53418900000000000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Uruguay</td>\n",
       "        <td>3.2863140000000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Venezuela</td>\n",
       "        <td>28.9461010000000000</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Argentina', Decimal('42.6695000000000000')),\n",
       " ('Bolivia', Decimal('10.0272540000000000')),\n",
       " ('Brazil', Decimal('202.7940000000000000')),\n",
       " ('Chile', Decimal('17.7730000000000000')),\n",
       " ('Colombia', Decimal('47.6620000000000000')),\n",
       " ('Ecuador', Decimal('15.7742000000000000')),\n",
       " ('Guyana', Decimal('0.78489400000000000000')),\n",
       " ('Paraguay', Decimal('6.7833740000000000')),\n",
       " ('Peru', Decimal('30.4751440000000000')),\n",
       " ('Saint Vincent and the Grenadines', Decimal('0.10900000000000000000')),\n",
       " ('Suriname', Decimal('0.53418900000000000000')),\n",
       " ('Uruguay', Decimal('3.2863140000000000')),\n",
       " ('Venezuela', Decimal('28.9461010000000000'))]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, population/1000000 AS population FROM world WHERE continent='South America';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. France, Germany, Italy\n",
    "\n",
    "Show the `name` and `population` for France, Germany, Italy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>population</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>France</td>\n",
       "        <td>65906000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Germany</td>\n",
       "        <td>80716000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>60782668</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('France', Decimal('65906000')),\n",
       " ('Germany', Decimal('80716000')),\n",
       " ('Italy', Decimal('60782668'))]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, population FROM world WHERE name IN ('France', 'Germany', 'Italy');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. United\n",
    "\n",
    "Show the countries which have a `name` that includes the word 'United'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United Arab Emirates</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United States</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('United Arab Emirates',), ('United Kingdom',), ('United States',)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name FROM world WHERE name LIKE '%United%';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Two ways to be big\n",
    "\n",
    "Two ways to be big: A country is **big** if it has an area of more than 3 million sq km or it has a population of more than 250 million.\n",
    "\n",
    "**Show the countries that are big by area or big by population. Show name, population and area.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "8 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>population</th>\n",
       "        <th>area</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Australia</td>\n",
       "        <td>23545500</td>\n",
       "        <td>7692024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brazil</td>\n",
       "        <td>202794000</td>\n",
       "        <td>8515767</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Canada</td>\n",
       "        <td>35427524</td>\n",
       "        <td>9984670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>1365370000</td>\n",
       "        <td>9596961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>India</td>\n",
       "        <td>1246160000</td>\n",
       "        <td>3166414</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Indonesia</td>\n",
       "        <td>252164800</td>\n",
       "        <td>1904569</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Russia</td>\n",
       "        <td>146000000</td>\n",
       "        <td>17125242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United States</td>\n",
       "        <td>318320000</td>\n",
       "        <td>9826675</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Australia', Decimal('23545500'), Decimal('7692024')),\n",
       " ('Brazil', Decimal('202794000'), Decimal('8515767')),\n",
       " ('Canada', Decimal('35427524'), Decimal('9984670')),\n",
       " ('China', Decimal('1365370000'), Decimal('9596961')),\n",
       " ('India', Decimal('1246160000'), Decimal('3166414')),\n",
       " ('Indonesia', Decimal('252164800'), Decimal('1904569')),\n",
       " ('Russia', Decimal('146000000'), Decimal('17125242')),\n",
       " ('United States', Decimal('318320000'), Decimal('9826675'))]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, population, area FROM world\n",
    "WHERE population >= 250000000 OR area >= 3000000;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. One or the other (but not both)\n",
    "\n",
    "**Exclusive OR (XOR). Show the countries that are big by area (more than 3 million) or big by population (more than 250 million) but not both. Show name, population and area.**\n",
    "\n",
    "- Australia has a big area but a small population, it should be **included**.\n",
    "- Indonesia has a big population but a small area, it should be **included**.\n",
    "- China has a big population **and** big area, it should be **excluded**.\n",
    "- United Kingdom has a small population and a small area, it should be **excluded**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "Done.\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>population</th>\n",
       "        <th>area</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Australia</td>\n",
       "        <td>23545500</td>\n",
       "        <td>7692024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brazil</td>\n",
       "        <td>202794000</td>\n",
       "        <td>8515767</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Canada</td>\n",
       "        <td>35427524</td>\n",
       "        <td>9984670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Indonesia</td>\n",
       "        <td>252164800</td>\n",
       "        <td>1904569</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Russia</td>\n",
       "        <td>146000000</td>\n",
       "        <td>17125242</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Australia', Decimal('23545500'), Decimal('7692024')),\n",
       " ('Brazil', Decimal('202794000'), Decimal('8515767')),\n",
       " ('Canada', Decimal('35427524'), Decimal('9984670')),\n",
       " ('Indonesia', Decimal('252164800'), Decimal('1904569')),\n",
       " ('Russia', Decimal('146000000'), Decimal('17125242'))]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "-- postgresql does not have XOR operator\n",
    "CREATE OR REPLACE FUNCTION fxor (x boolean, y boolean) RETURNS boolean AS \n",
    "$BODY$\n",
    "BEGIN\n",
    "  RETURN (x OR y) AND NOT (x AND y);\n",
    "END;\n",
    "$BODY$\n",
    "LANGUAGE PLPGSQL;\n",
    "\n",
    "SELECT name, population, area FROM world\n",
    "WHERE fxor(population >= 250000000, area >= 3000000);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9. Rounding\n",
    "\n",
    "Show the `name` and `population` in millions and the GDP in billions for the countries of the `continent` 'South America'. Use the [ROUND](https://sqlzoo.net/wiki/ROUND) function to show the values to two decimal places.\n",
    "\n",
    "**For South America show population in millions and GDP in billions both to 2 decimal places.**\n",
    "\n",
    "> _Millions and billions_    \n",
    "> Divide by 1000000 (6 zeros) for millions. Divide by 1000000000 (9 zeros) for billions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "13 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>popl</th>\n",
       "        <th>gdp_mil</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Argentina</td>\n",
       "        <td>42.67</td>\n",
       "        <td>477.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bolivia</td>\n",
       "        <td>10.03</td>\n",
       "        <td>27.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brazil</td>\n",
       "        <td>202.79</td>\n",
       "        <td>2254.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Chile</td>\n",
       "        <td>17.77</td>\n",
       "        <td>268.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Colombia</td>\n",
       "        <td>47.66</td>\n",
       "        <td>369.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ecuador</td>\n",
       "        <td>15.77</td>\n",
       "        <td>87.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Guyana</td>\n",
       "        <td>0.78</td>\n",
       "        <td>2.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Paraguay</td>\n",
       "        <td>6.78</td>\n",
       "        <td>25.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Peru</td>\n",
       "        <td>30.48</td>\n",
       "        <td>204.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Saint Vincent and the Grenadines</td>\n",
       "        <td>0.11</td>\n",
       "        <td>0.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Suriname</td>\n",
       "        <td>0.53</td>\n",
       "        <td>5.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Uruguay</td>\n",
       "        <td>3.29</td>\n",
       "        <td>49.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Venezuela</td>\n",
       "        <td>28.95</td>\n",
       "        <td>382.42</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Argentina', Decimal('42.67'), Decimal('477.03')),\n",
       " ('Bolivia', Decimal('10.03'), Decimal('27.04')),\n",
       " ('Brazil', Decimal('202.79'), Decimal('2254.11')),\n",
       " ('Chile', Decimal('17.77'), Decimal('268.31')),\n",
       " ('Colombia', Decimal('47.66'), Decimal('369.81')),\n",
       " ('Ecuador', Decimal('15.77'), Decimal('87.50')),\n",
       " ('Guyana', Decimal('0.78'), Decimal('2.85')),\n",
       " ('Paraguay', Decimal('6.78'), Decimal('25.94')),\n",
       " ('Peru', Decimal('30.48'), Decimal('204.68')),\n",
       " ('Saint Vincent and the Grenadines', Decimal('0.11'), Decimal('0.69')),\n",
       " ('Suriname', Decimal('0.53'), Decimal('5.01')),\n",
       " ('Uruguay', Decimal('3.29'), Decimal('49.92')),\n",
       " ('Venezuela', Decimal('28.95'), Decimal('382.42'))]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, ROUND(population/1000000, 2) as popl, ROUND(gdp/1000000000, 2) as gdp_mil\n",
    "FROM world\n",
    "WHERE continent = 'South America';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10. Trillion dollar economies\n",
    "\n",
    "Show the `name` and per-capita GDP for those countries with a GDP of at least one trillion (1000000000000; that is 12 zeros). Round this value to the nearest 1000.\n",
    "\n",
    "**Show per-capita GDP for the trillion dollar countries to the nearest $1000.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "15 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>pc_gdp</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Australia</td>\n",
       "        <td>66000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brazil</td>\n",
       "        <td>11000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Canada</td>\n",
       "        <td>45000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>6000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>France</td>\n",
       "        <td>40000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Germany</td>\n",
       "        <td>42000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>India</td>\n",
       "        <td>2000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>33000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Japan</td>\n",
       "        <td>47000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mexico</td>\n",
       "        <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Russia</td>\n",
       "        <td>14000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>South Korea</td>\n",
       "        <td>22000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>28000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United Kingdom</td>\n",
       "        <td>39000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United States</td>\n",
       "        <td>51000</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Australia', Decimal('66000')),\n",
       " ('Brazil', Decimal('11000')),\n",
       " ('Canada', Decimal('45000')),\n",
       " ('China', Decimal('6000')),\n",
       " ('France', Decimal('40000')),\n",
       " ('Germany', Decimal('42000')),\n",
       " ('India', Decimal('2000')),\n",
       " ('Italy', Decimal('33000')),\n",
       " ('Japan', Decimal('47000')),\n",
       " ('Mexico', Decimal('10000')),\n",
       " ('Russia', Decimal('14000')),\n",
       " ('South Korea', Decimal('22000')),\n",
       " ('Spain', Decimal('28000')),\n",
       " ('United Kingdom', Decimal('39000')),\n",
       " ('United States', Decimal('51000'))]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, ROUND(gdp/(population*1000), 0) * 1000 AS pc_gdp\n",
    "FROM world\n",
    "WHERE gdp >= POWER(10, 12);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 11. Name and capital have the same length\n",
    "\n",
    "Greece has capital Athens.\n",
    "\n",
    "Each of the strings 'Greece', and 'Athens' has 6 characters.\n",
    "\n",
    "**Show the name and capital where the name and the capital have the same number of characters.**\n",
    "\n",
    "- You can use the [LENGTH](https://sqlzoo.net/wiki/LENGTH) function to find the number of characters in a string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "26 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>capital</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Algeria</td>\n",
       "        <td>Algiers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Angola</td>\n",
       "        <td>Luanda</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Armenia</td>\n",
       "        <td>Yerevan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Botswana</td>\n",
       "        <td>Gaborone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Canada</td>\n",
       "        <td>Ottowa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Djibouti</td>\n",
       "        <td>Djibouti</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Egypt</td>\n",
       "        <td>Cairo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Estonia</td>\n",
       "        <td>Tallinn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Fiji</td>\n",
       "        <td>Suva</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Gambia</td>\n",
       "        <td>Banjul</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Georgia</td>\n",
       "        <td>Tbilisi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ghana</td>\n",
       "        <td>Accra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Greece</td>\n",
       "        <td>Athens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Luxembourg</td>\n",
       "        <td>Luxembourg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mauritania</td>\n",
       "        <td>Nouakchott</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Paraguay</td>\n",
       "        <td>Asunción</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Peru</td>\n",
       "        <td>Lima</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Poland</td>\n",
       "        <td>Warsaw</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Russia</td>\n",
       "        <td>Moscow</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Rwanda</td>\n",
       "        <td>Kigali</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">26 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('Algeria', 'Algiers'),\n",
       " ('Angola', 'Luanda'),\n",
       " ('Armenia', 'Yerevan'),\n",
       " ('Botswana', 'Gaborone'),\n",
       " ('Canada', 'Ottowa'),\n",
       " ('Djibouti', 'Djibouti'),\n",
       " ('Egypt', 'Cairo'),\n",
       " ('Estonia', 'Tallinn'),\n",
       " ('Fiji', 'Suva'),\n",
       " ('Gambia', 'Banjul'),\n",
       " ('Georgia', 'Tbilisi'),\n",
       " ('Ghana', 'Accra'),\n",
       " ('Greece', 'Athens'),\n",
       " ('Luxembourg', 'Luxembourg'),\n",
       " ('Mauritania', 'Nouakchott'),\n",
       " ('Paraguay', 'Asunción'),\n",
       " ('Peru', 'Lima'),\n",
       " ('Poland', 'Warsaw'),\n",
       " ('Russia', 'Moscow'),\n",
       " ('Rwanda', 'Kigali'),\n",
       " ('San Marino', 'San Marino'),\n",
       " ('Singapore', 'Singapore'),\n",
       " ('Taiwan', 'Taipei'),\n",
       " ('Togo', 'Lomé'),\n",
       " ('Turkey', 'Ankara'),\n",
       " ('Zambia', 'Lusaka')]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, capital FROM world\n",
    "WHERE LENGTH(name) = LENGTH(capital);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12. Matching name and capital\n",
    "\n",
    "The capital of Sweden is Stockholm. Both words start with the letter 'S'.\n",
    "\n",
    "**Show the name and the capital where the first letters of each match. Don't include countries where the name and the capital are the same word.**\n",
    "\n",
    "- You can use the function [LEFT](https://sqlzoo.net/wiki/LEFT) to isolate the first character.\n",
    "- You can use <> as the **NOT EQUALS** operator."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "24 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>capital</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Algeria</td>\n",
       "        <td>Algiers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Andorra</td>\n",
       "        <td>Andorra la Vella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Barbados</td>\n",
       "        <td>Bridgetown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Belize</td>\n",
       "        <td>Belmopan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brazil</td>\n",
       "        <td>Brasília</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brunei</td>\n",
       "        <td>Bandar Seri Begawan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Burundi</td>\n",
       "        <td>Bujumbura</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Guatemala</td>\n",
       "        <td>Guatemala City</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Guyana</td>\n",
       "        <td>Georgetown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Kuwait</td>\n",
       "        <td>Kuwait City</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Maldives</td>\n",
       "        <td>Malé</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Marshall Islands</td>\n",
       "        <td>Majuro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mexico</td>\n",
       "        <td>Mexico City</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Monaco</td>\n",
       "        <td>Monaco-Ville</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mozambique</td>\n",
       "        <td>Maputo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Niger</td>\n",
       "        <td>Niamey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Panama</td>\n",
       "        <td>Panama City</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Papua New Guinea</td>\n",
       "        <td>Port Moresby</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sao Tomé and Príncipe</td>\n",
       "        <td>São Tomé</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>South Korea</td>\n",
       "        <td>Seoul</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">24 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('Algeria', 'Algiers'),\n",
       " ('Andorra', 'Andorra la Vella'),\n",
       " ('Barbados', 'Bridgetown'),\n",
       " ('Belize', 'Belmopan'),\n",
       " ('Brazil', 'Brasília'),\n",
       " ('Brunei', 'Bandar Seri Begawan'),\n",
       " ('Burundi', 'Bujumbura'),\n",
       " ('Guatemala', 'Guatemala City'),\n",
       " ('Guyana', 'Georgetown'),\n",
       " ('Kuwait', 'Kuwait City'),\n",
       " ('Maldives', 'Malé'),\n",
       " ('Marshall Islands', 'Majuro'),\n",
       " ('Mexico', 'Mexico City'),\n",
       " ('Monaco', 'Monaco-Ville'),\n",
       " ('Mozambique', 'Maputo'),\n",
       " ('Niger', 'Niamey'),\n",
       " ('Panama', 'Panama City'),\n",
       " ('Papua New Guinea', 'Port Moresby'),\n",
       " ('Sao Tomé and Príncipe', 'São Tomé'),\n",
       " ('South Korea', 'Seoul'),\n",
       " ('Sri Lanka', 'Sri Jayawardenepura Kotte'),\n",
       " ('Sweden', 'Stockholm'),\n",
       " ('Taiwan', 'Taipei'),\n",
       " ('Tunisia', 'Tunis')]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, capital FROM world\n",
    "WHERE LEFT(name, 1) = LEFT(capital, 1) AND name <> capital;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 13. All the vowels\n",
    "\n",
    "**Equatorial Guinea** and **Dominican Republic** have all of the vowels (a e i o u) in the name. They don't count because they have more than one word in the name.\n",
    "\n",
    "**Find the country that has all the vowels and no spaces in its name.**\n",
    "\n",
    "- You can use the phrase name `NOT LIKE '%a%'` to exclude characters from your results.\n",
    "- The query shown misses countries like Bahamas and Belarus because they contain at least one 'a'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mozambique</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Mozambique',)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name FROM world\n",
    "WHERE name LIKE '%a%' AND name LIKE '%e%' AND name LIKE '%i%' AND name LIKE '%o%' AND name LIKE '%u%'\n",
    "  AND name NOT LIKE '% %';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
